﻿using System;
using System.Linq;
using System.Reflection;
using TallyBook.DAL.Mode;

namespace TallyBook.DAL.Repository
{
    /// <summary>
    /// 构造sql语句
    /// </summary>
    public class SqlBuilder
    {
        public static string Get<TEntity>(long id)
        {
            return "select  *  from  [" + typeof(TEntity).Name + "] where id=" +id;
        }

        public static string InsertBuild<TEntity>(TEntity entity)
        {
            string sqlStr = "insert into [" + typeof(TEntity).Name + "] (";
            var propertie = typeof(TEntity).GetProperties();

            foreach (var property in propertie)
            {
                sqlStr += "[" + property.Name + "],";
            }

            sqlStr = sqlStr.Remove(sqlStr.Length - 1);
            sqlStr += ") values (";
            foreach (var property in propertie)
            {
                var value = property.GetValue(entity);
                sqlStr += "'" + value + "',";
            }
            sqlStr = sqlStr.Remove(sqlStr.Length - 1);
            sqlStr += ")";

            return sqlStr;
        }

        public static string UpdateBuild<TEntity>(TEntity entity)
        {
            var sqlStr = "update[" + typeof(TEntity).Name + "]set";
            var propertise = typeof(TEntity).GetProperties().ToList();

            //取出普通属性
            var normalPropertie = propertise.Where(property =>
            {
                var attrs = property.GetCustomAttributes(typeof(PrimaryKeyAttribute), false);
                if (attrs.Length == 0)
                    return true;
                var primaryKey = attrs[0] as PrimaryKeyAttribute;
                if (primaryKey == null)
                    return true;
                return false;
            }).ToList();

            //生成where前面部分的sql
            normalPropertie.ForEach(property =>
            {
                sqlStr += "[" + property.Name + "]=";
                var value = property.GetValue(entity);
                sqlStr += "'" + value + "',";
            });
            sqlStr = sqlStr.Remove(sqlStr.Length - 1);

            //找出主键
            var primaryKeys = propertise.Where(property => !normalPropertie.Contains(property)).ToList();
            if (primaryKeys.Count == 0)
                return sqlStr;

            //根据主键查询
            sqlStr += "where ";
            primaryKeys.ForEach(item =>
            {
                sqlStr += "[" + item.Name + "]='" + item.GetValue(entity) + "'" + "and ";
            });
            sqlStr = sqlStr.Remove(sqlStr.Length - 4, 4);
            return sqlStr;
        }

        public static string DeleteBuild<TEntity>(TEntity entity)
        {
            var sqlStr = "delete from [" + typeof(TEntity).Name + "] where";
            var propertise = typeof(TEntity).GetProperties().ToList();

            //找出主键
            var primaryKeys = propertise.Where(property =>
            {
                var attrs = property.GetCustomAttributes(typeof(PrimaryKeyAttribute), false);
                if (attrs.Length == 0)
                    return false;
                var primaryKey = attrs[0] as PrimaryKeyAttribute;
                if (primaryKey == null)
                    return false;
                return true;
            }).ToList();
            if (primaryKeys.Count == 0)
                return sqlStr;

            //根据主键查询
            primaryKeys.ForEach(item =>
            {
                sqlStr += "[" + item.Name + "]='" + item.GetValue(entity) + "'" + "and ";
            });
            sqlStr = sqlStr.Remove(sqlStr.Length - 4, 4);
            return sqlStr;
        }

        public static string GetAllBuild<TEntity>()
        {
            return "select * from [" + typeof(TEntity).Name + "] ";
        }

        private static string GetPremaryKey(Type entity)
        {
            return entity.GetProperties()
                .Select(item =>
                {
                    var attrs = item.GetCustomAttributes(typeof(PrimaryKeyAttribute), false);
                    if (attrs.Length == 0)
                        return null;
                    return item.Name;
                }).FirstOrDefault(item => item != null);
        }

        #region 链表复杂查询
     /// <summary>
     ///  链表复杂查询
     /// </summary>
     /// <param name="entity"></param>
     /// <param name="id"></param>
     /// <returns></returns>
        public static string GetComplex(Type entity , long id)
        {
            string sqlStr = "select ";

            string tableStr = " from " + entity.Name;

            entity.GetProperties()
            .ToList()
            .ForEach(property =>
            {
                if (property.PropertyType.IsValueType || property.PropertyType == typeof(string))
                    sqlStr += SelectFied(property) + ",";
                else
                {
                    property.PropertyType.GetProperties()
                         .ToList().ForEach(item =>
                         {
                             sqlStr += SelectFied(item) + ",";
                         });

                    tableStr += " inner join " + property.PropertyType.Name + " on " +
                        entity.Name + "." + GetPremaryKey(entity) + "="
                        + property.PropertyType.Name + "." + GetPremaryKey(property.PropertyType);
                }

            });
            sqlStr = sqlStr.Remove(sqlStr.Length - 1);

            sqlStr += tableStr;

            sqlStr += " where " + entity.Name + "." + GetPremaryKey(entity) + "=" + id;

            return sqlStr;

        }

        private static string SelectFied(PropertyInfo property)
        {

            return string.Format("{0}.{1} as  {0}_{1}", property.DeclaringType.Name, property.Name);
        }

        public static string GetComplex<TEntity>()
        {
            string sqlStr = "select ";

            string tableStr = " from " + typeof(TEntity).Name;

            typeof(TEntity).GetProperties()
            .ToList()
            .ForEach(property =>
            {
                if (property.PropertyType.IsValueType || property.PropertyType == typeof(string))
                    sqlStr += SelectFied(property) + ",";
                else
                {
                    property.PropertyType.GetProperties()
                         .ToList().ForEach(item =>
                         {
                             sqlStr += SelectFied(item) + ",";
                         });

                    tableStr += " inner join " + property.PropertyType.Name + " on " +
                        typeof(TEntity).Name + "." + GetPremaryKey(typeof(TEntity)) + "="
                        + property.PropertyType.Name + "." + GetPremaryKey(property.PropertyType);
                }

            });
            sqlStr = sqlStr.Remove(sqlStr.Length - 1);

            sqlStr += tableStr;
            
            return sqlStr;

        }
        #endregion
    }
}
